﻿Imports System.Data.SqlClient
Public Class frmPrograms
    Dim CN As New SqlConnection
    Dim intID As Integer
    Private Sub Programs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        CN.ConnectionString = My.Settings.IT308ConnectionString
        Load_cboProgram()
    End Sub

    Private Sub btnAddProgram_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddProgram.Click
        'WB - Checks to make sure data is entered Inputbox
        Dim strProgramName As String
        strProgramName = InputBox("Enter the desired name of the Program", "New Program")
        If strProgramName = " " Then
            MessageBox.Show("No name entered", "Error")
        Else
            'WB - declare and instantiate neccesary variables to connect to Database
            Dim intRows As Integer
            Dim cmd As New SqlCommand
            cmd.Connection = CN
            'WB - Tries to connect to the database - if unsuccessfull it reports error and closes program
            Try
                CN.Open()
            Catch ex As Exception
                MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
                Me.Close()
            End Try

            'WB - Sets up the New Command to enter the Name into the PROGRAM Table
            Try
                cmd.CommandText = "INSERT PROGRAM (Name) VALUES (@Name)"
                cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50)
                cmd.Parameters("@Name").Value = strProgramName.Trim

                'WB - Performs the Execution
                intRows = cmd.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox("Error entering Program into System - " & ex.Message)
            End Try

            'WB - Reports to the user if the add statement failed
            If intRows = 0 Then
                MsgBox("Error entering Program into system")
            End If

            'WB - Closes connection with the database if it is still open
            If CN.State = ConnectionState.Open Then CN.Close()
            Load_cboProgram()
        End If
    End Sub
    Private Sub Load_cboProgram()
        'WB - Creates the connection to the Database to load Program names into cboProgram
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "SELECT Name FROM PROGRAM ORDER BY Name"
        Dim Reader As SqlDataReader

        'WB - Tries to connect to the database - if unsuccessfull it reports error and closes the window
        Try
            CN.Open()
        Catch ex As Exception
            MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
            Me.Close()
        End Try

        'WB - Reads program names from the database and enters them into cboPrograms after clearing
        cboProgram.Items.Clear()
        Reader = cmd.ExecuteReader
        While Reader.Read
            cboProgram.Items.Add(Convert.ToString(Reader.Item("Name")).Trim)
        End While

        'WB - Closes connection with the database if it is still open
        If CN.State = ConnectionState.Open Then CN.Close()
    End Sub
    Private Sub Load_lstAllCourses()
        'WB - Creates the command to load course IDs and names into lstAllCourses
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "SELECT ID, Name FROM COURSES"
        Dim Reader As SqlDataReader

        'WB - Tries to connect to the database - if unsuccessfull it reports error and closes the window
        Try
            CN.Open()
        Catch ex As Exception
            MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
            Me.Close()
        End Try

        'WB - Reads IDs and Names from the COURSES Table and enters them into lstAllCourses after clearing
        Reader = cmd.ExecuteReader
        lstAllCourses.Items.Clear()
        While Reader.Read
            lstAllCourses.Items.Add(Convert.ToString(Reader.Item("ID")).Trim & " " & Convert.ToString(Reader.Item("Name")).Trim)
        End While

        'WB - Closes connection with the database if it is still open
        If CN.State = ConnectionState.Open Then CN.Close()

        'WB - removes the items from lstAllCourses that are already in lstProgramCourses.
        For Each Item In lstProgramCourses.Items
            lstAllCourses.Items.Remove(Item)
        Next

    End Sub

    Private Sub Load_lstProgramCourses()
        'WB - Creates the command to load courseIDs into lstProgramCourses
        Dim cmd As New SqlCommand
        cmd.Connection = CN
        cmd.CommandText = "SELECT COURSES.ID, COURSES.Name From COURSES INNER JOIN PROGRAMCOURSES ON COURSES.ID = PROGRAMCOURSES.CoursesID WHERE PROGRAMCOURSES.ProgramID = @ProgramID"
        Dim Reader As SqlDataReader

        'WB - Tries to connect to the database - if unsuccessfull it reports error and closes the window
        Try
            CN.Open()
        Catch ex As Exception
            MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
            Me.Close()
        End Try

        'WB - Reads IDs from the COURSES Table and enters them into lstProgramcourses after clearing
        cmd.Parameters.Add("@ProgramID", SqlDbType.Int)
        cmd.Parameters("@ProgramID").Value = intID
        Reader = cmd.ExecuteReader
        lstProgramCourses.Items.Clear()
        While Reader.Read
            lstProgramCourses.Items.Add(Convert.ToString(Reader.Item("ID")).Trim & " " & Convert.ToString(Reader.Item("Name")).Trim)
        End While

        'WB - Closes connection with the database if it is still open
        If CN.State = ConnectionState.Open Then CN.Close()
    End Sub

    Private Sub cboProgram_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboProgram.SelectedIndexChanged
        'WB - First checks to make sure that an item has been selected.
        If cboProgram.SelectedIndex = -1 Then
            MessageBox.Show("Please Select a Program to Procede", "Make a Selection")
        Else
            'WB - First gets the Program ID and stores it into a Global Vaialble intID
            Dim cmd As New SqlCommand
            cmd.Connection = CN
            cmd.CommandText = "SELECT ID FROM PROGRAM WHERE Name = @Name"
            Dim Reader As SqlDataReader
            'WB - Tries to connect to the database - if unsuccessfull it reports error and closes the window
            Try
                CN.Open()
            Catch ex As Exception
                MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
                Me.Close()
            End Try

            'WB - Reads ProgramID from the PROGRAM Table 
            cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50)
            cmd.Parameters("@Name").Value = cboProgram.SelectedItem.ToString.Trim
            Reader = cmd.ExecuteReader
            While Reader.Read
                intID = Integer.Parse(CStr(Reader.Item("ID")))
            End While

            'WB - Closes connection with the database if it is still open
            If CN.State = ConnectionState.Open Then CN.Close()

            Load_lstProgramCourses()
            Load_lstAllCourses()
        End If
    End Sub

    Private Sub btnAddCourse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddCourse.Click
        'WB - Verify that an item has been selected to add.
        If lstAllCourses.SelectedIndex = -1 Then
            MessageBox.Show("No Course Selected to Add", "Error")
        Else
            'WB - declare and instantiate neccesary variables to connect to Database
            Dim intRows As Integer
            Dim cmd As New SqlCommand
            cmd.Connection = CN

            'WB - Tries to connect to the database - if unsuccessfull it reports error and closes program
            Try
                CN.Open()
            Catch ex As Exception
                MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
                Me.Close()
            End Try

            'WB - Sets up the New Command to enter the Record into PROGRAMCOURSES
            Try
                cmd.CommandText = "INSERT PROGRAMCOURSES (CoursesID, ProgramID) VALUES (@CoursesID, @ProgramID)"
                cmd.Parameters.Add("@CoursesID", SqlDbType.VarChar, 6)
                cmd.Parameters.Add("@ProgramID", SqlDbType.Int)
                cmd.Parameters("@CoursesID").Value = lstAllCourses.SelectedItem.ToString.Substring(0, lstAllCourses.SelectedItem.ToString.IndexOf(" "))
                cmd.Parameters("@ProgramID").Value = intID

                'WB - Performs the Execution
                intRows = cmd.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox("Error adding course into Program Curriculum - " & ex.Message)
            End Try

            'WB - Reports to the user if the add statement failed
            If intRows = 0 Then
                MsgBox("Error adding course into Program Curriculum")
            End If

            'WB - Closes connection with the database if it is still open
            If CN.State = ConnectionState.Open Then CN.Close()
            'WB - refreshes the list boxes
            lstProgramCourses.Items.Add(lstAllCourses.SelectedItem)
            lstAllCourses.Items.Remove(lstAllCourses.SelectedItem)
        End If
    End Sub

    Private Sub btnRemoveCourse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRemoveCourse.Click
        'WB - Verify that an item has been selected to remove.
        If lstProgramCourses.SelectedIndex = -1 Then
            MessageBox.Show("No Course Selected to Remove", "Error")
        Else
            'WB - declare and instantiate neccesary variables to connect to Database
            Dim cmd As New SqlCommand
            cmd.Connection = CN
            Dim intRows As Integer

            'WB - Tries to connect to the database - if unsuccessfull it reports error and closes program
            Try
                CN.Open()
            Catch ex As Exception
                MsgBox("Cannot Connect to Data Base: Error - " & ex.Message)
                Me.Close()
            End Try

            'WB - Sets up the New Command to enter the Record into PROGRAMCOURSES
            Try
                cmd.CommandText = "DELETE PROGRAMCOURSES WHERE CoursesID = @CoursesID AND ProgramID = @ProgramID"
                cmd.Parameters.Add("@CoursesID", SqlDbType.VarChar, 6)
                cmd.Parameters.Add("@ProgramID", SqlDbType.Int)
                cmd.Parameters("@CoursesID").Value = lstProgramCourses.SelectedItem.ToString.Substring(0, lstProgramCourses.SelectedItem.ToString.IndexOf(" "))
                cmd.Parameters("@ProgramID").Value = intID

                'WB - Performs the Execution
                intRows = cmd.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox("Error removing course from Program Curriculum - " & ex.Message)
            End Try

            'WB - Reports to the user if the add statement failed
            If intRows = 0 Then
                MsgBox("Error removing course from Program Curriculum")
            End If

            'WB - Closes connection with the database if it is still open
            If CN.State = ConnectionState.Open Then CN.Close()
            'WB -  refreshes the list boxes
            Load_lstProgramCourses()
            Load_lstAllCourses()
        End If
    End Sub
End Class